﻿using System;
using System.Drawing;
using System.Collections.Generic;
using System.Linq;
using System.Data.SqlServerCe;
using System.Text;
using System.Windows.Forms;


namespace GraphsLab5
{
    class SQLConnection
    {
        private const string CONNECTION_STRING = "Data Source='DB.sdf';";

        public static List<Point> getPoints(){

            List<Point> list = new List<Point>();

            using (var connection = new SqlCeConnection(CONNECTION_STRING))
            {
                using (var command = new SqlCeCommand(
                    "SELECT X,Y FROM Graphs", connection))
                {
                    connection.Open();
                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            int x = reader.GetInt32(0);
                            int y = reader.GetInt32(1);
                            list.Add(new Point(x, y));
                        }
                    }
                }
            }

            return list;

        }

        public static List<Color> getColors(){

            List<Color> list = new List<Color>();

            using (var connection = new SqlCeConnection(CONNECTION_STRING))
            {
                using (var command = new SqlCeCommand(
                    "SELECT Color FROM Graphs", connection))
                {
                    connection.Open();
                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            Color c = new Color();
                            string color = reader.GetString(0);
                            switch (color)
                            {
                                case "Black": { c = Color.Black; break; }
                                case "Red": { c = Color.Red; break; }
                                case "Green": { c = Color.Green; break; }
                                case "Blue": { c = Color.Blue; break; }
                                default: { c = Color.Black; break; }
                            }
                            list.Add(c);
                        }
                    }
                }
            }

            return list;

        }

        public static List<List<int> > getLines()
        {

            List<List<int> > list = new List<List<int> >();

            using (var connection = new SqlCeConnection(CONNECTION_STRING))
            {
                using (var command = new SqlCeCommand(
                    "SELECT Lines FROM Graphs", connection))
                {
                    connection.Open();
                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            string allLines;
                            List<int> add = new List<int>();
                            if (!reader.IsDBNull(0))
                            {
                                allLines = reader.GetString(0);
                                if (allLines.Length > 0)
                                {
                                    foreach (string s in allLines.Split(','))
                                    {
                                        add.Add(Int32.Parse(s));
                                    }
                                }
                            }
                            list.Add(add);
                        }
                    }
                }
            }

            return list;

        }


        public static bool saveDB(List<Point> points, List<Color> colors, List<List<int>> lines)
        {
            try
            {

                using (var connection = new SqlCeConnection(CONNECTION_STRING))
                {
                    using (var command = new SqlCeCommand(
                    "DELETE FROM Graphs", connection))
                    {
                        connection.Open();
                        using (var reader = command.ExecuteReader())
                        {
                            while (reader.Read()) ;
                        }
                    }
                }
            }
            catch
            {
                MessageBox.Show("Error in DB (while deleting items)!");
            }
            try{
                    for (int i = 0; i < points.Count; i++)
                    {
                        string query = "INSERT INTO Graphs VALUES (" + points[i].X + "," + points[i].Y + ",'" + colors[i].Name + "','";
                        if (lines[i].Count > 0)
                        {
                            for (int j = 0; j < lines[i].Count; j++)
                            {
                                query += lines[i][j].ToString() + ",";
                            }
                            query = query.Remove(query.Length - 1);
                        }
                        query += "');";
                        using (var connection = new SqlCeConnection(CONNECTION_STRING))
                        {
                            using (var command = new SqlCeCommand(
                            query, connection))
                            {
                                connection.Open();
                                using (var reader = command.ExecuteReader())
                                {
                                    while (reader.Read());
                                }
                            }
                        }
                    }
                    
                }

            catch (Exception e)
            {
                MessageBox.Show("Error in DB (while addind items)\n" + e.Message);
                return false;
            }
            return true;
        }

    }
}
